<?php 
	jimport('joomla.application.component.model');
	class sale_tsc_thu_nganModelsu_luoc_thu_ngan extends JModel
	{
	   	    //tong tien trong ngay
        function tong_tien_trong_ngay()
        {
            $date = date('Y-m-d');
            $sql = "select sum(total) from #__erp_sale where date LIKE '$date%' AND  status = 4
            ";
            $db = &JFactory::getDBO();
            $db->setQuery($sql);
            $rs= $db->loadResult();
            return $rs;
        }
        
	    //tong tien tai cua hang
        function tong_tien_truoc_den_gio()
        {
            $sql = "select sum(total) from #__erp_sale where status = 4";
            $db = &JFactory::getDBO();
            $db->setQuery($sql);
            $sum_truoc_den_gio = $db->loadResult();
            return $sum_truoc_den_gio;
        }
        
	   //cap nhat so luong
        function cap_nhat_lai_so_luong($id_pro,$id_wh,$num)
        {
            
            $db = &JFactory::getDBO();
            $sql = "select * from #__erp_warehouse_ex_pro 
            where 
            id_product = '".$id_pro."' 
            and id_warehouse = '".$id_wh."'
            and number_of > 0
            ORDER BY date_in
            ";
			$db->setQuery($sql);
            $data = $db->loadObjectList();	
            foreach($data as $row)
            {
                $num = $row->number_of+$num;
                    $sql = "UPDATE #__erp_warehouse_ex_pro SET
                                number_of = '".$num."'
                                WHERE 
                                id ='".$row->id."'";
                                $db->setQuery($sql);
                                $db->query();
                    return;
            }    
        }	   
        
        //cap nhat lai stt
        function cap_nhat_stt_sale_order($id_bill)
        {
            $db = &JFactory::getDBO();
            $sql = "SELECT *
                    FROM #__erp_sale_order od
                    WHERE id_order = '".$id_bill."'
                ";
            $db->setQuery($sql);
            //print_r($db->loadObjectList());die;
            $data = $db->loadObjectList();   
            foreach($data as $row)
            {
                $sql = "UPDATE #__erp_sale_order SET
                                status = 2
                                WHERE 
                                id ='".$row->id."'";
                                $db->setQuery($sql);
                                $db->query();
            }
        }	   
        
        //danh sach sp theo bill
        function ds_bill($id_bill)
        {
            $db = &JFactory::getDBO();
            $sql = "SELECT ep.name as pro_name, od.number, od.status, ep.id as id_pro
                    FROM #__erp_sale_order od, #__erp_product ep
                    WHERE id_order = '".$id_bill."'
                    AND ep.id = od.id_product
                ";
            $db->setQuery($sql);
            //print_r($db->loadObjectList());die;
            return $db->loadObjectList();    
        }
        
        //xoa buzy
        function xoa_buz($id)
        {
            $db = &JFactory::getDBO();
            $sql = "select id_order from #__erp_sale where id = '".$id."'";
            $db->setQuery($sql);
            $buz = $db->loadResult();
            $sql = "DELETE FROM #__erp_sale_order_buzy
                    WHERE id_order ='".$buz."'";
                                $db->setQuery($sql);
                                $db->query();        
        }
        	   
        function su_luoc_theo_ngay_stt_3($date=null)
        {

            //neu date = null thi lay ngay hien hanh
            if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }
            $db = &JFactory::getDBO();
            $sql = "SELECT sa.*, so.id_product, so.number, so.status as sp_st,ep.name, cu.id as id_cu, cu.symbol
                FROM #__erp_sale sa, #__erp_sale_order so, #__erp_product ep, #__erp_financial_currency cu
                WHERE date LIKE '$date%' 
                AND sa.id = so.id_order 
                AND sa.del = 0 
                AND sa.status = 3 
                AND ep.id = so.id_product          
                AND cu.id = ep.id_currency                     
                ORDER BY sa.id_order,sa.date ASC";
            $db->setQuery($sql);
            //print_r($db);            
            return $db->loadObjectList();
        }
           
        function su_luoc_theo_ngay_stt_1($date=null)
        {
            //neu date = null thi lay ngay hien hanh
            if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }
            $db = &JFactory::getDBO();
            $sql = "SELECT sa.*, so.id_product, so.number, so.status as sp_st,ep.name, cu.id as id_cu, cu.symbol
                FROM #__erp_sale sa, #__erp_sale_order so, #__erp_product ep, #__erp_financial_currency cu
                WHERE date LIKE '$date%' 
                AND sa.id = so.id_order  
                AND sa.status = 1 
                AND ep.id = so.id_product          
                AND cu.id = ep.id_currency                     
                ORDER BY sa.id_order,sa.date ASC";
            $db->setQuery($sql);
            //print_r($db);            
            return $db->loadObjectList();
        }   
        
        function su_luoc_theo_ngay_stt_2($date=null)
        {
            //neu date = null thi lay ngay hien hanh
            if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }
            $db = &JFactory::getDBO();
            $sql = "SELECT sa.*, so.id_product, so.number, so.status as sp_st,ep.name, cu.id as id_cu, cu.symbol
                FROM #__erp_sale sa, #__erp_sale_order so, #__erp_product ep, #__erp_financial_currency cu
                WHERE date LIKE '$date%' 
                AND sa.id = so.id_order 
                AND sa.del = 0 
                AND sa.status = 2 
                AND ep.id = so.id_product          
                AND cu.id = ep.id_currency                     
                ORDER BY sa.id_order,sa.date ASC";
            $db->setQuery($sql);
            //print_r($db);            
            return $db->loadObjectList();
        }   
	}
?>